【PostgreSQL一主两从集群搭建】

--- 主节点
1. 解压软件包
tar -xf pg16-4_centos.tar -C /data

2. 创建pg用户
useradd postgres

3. 指定数据目录所有者和所属组
chown -R postgres:postgres /data/pgsql/data/

4. 创建软链接
ln -s /data/pgsql /usr/local/pgsql

5. 进入pg用户
su postgres

6. 启动pgsql主节点
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/logfile.log start

7. 登录pgsql终端
/usr/local/pgsql/bin/psql -U postgres -d postgres

8. 修改密码和创建复制用户
postgres=# ALTER USER postgres WITH PASSWORD '5D0VieSajcmtBy';
postgres=# CREATE USER replica WITH REPLICATION ENCRYPTED PASSWORD '5D0VieSajcmtBy';

9. 添加 postgresql.conf 相关配置
# 监听所有IP地址
listen_addresses = '*'
# 设置服务端口
port = 5432
# 设置最大连接数
max_connections = 1000
# 启用wal归档
wal_level = replica
#wal_level = logical flink任务依赖
# 设置归档模式
archive_mode = on
archive_command = 'test ! -f /usr/local/pgsql/archive/%f && cp %p /usr/local/pgsql/archive/%f'
# 设置复制流
max_wal_senders = 200
wal_keep_size = 2GB # 根据业务情况调整至2GB
max_wal_size = 4GB   # 根据业务情况调整至4GB
min_wal_size = 1GB  # 根据业务情况调整至1GB
# 允许从节点进行只读查询
hot_standby = on
# 设置同步复制
synchronous_commit = on
synchronous_standby_names = '*'
# 开启日志收集
logging_collector = on
# 记录所有 SQL 语句
log_statement = 'all'
# 日志存放目录
log_directory = 'pg_log'
# 日志文件格式
log_filename = 'postgresql-%Y-%m-%d.log'

10. 添加 pg_hba.conf 相关配置
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
# slave node
#host    replication     replica         192.168.10.127/32       md5
#host    replication     replica         192.168.10.128/32       md5
host    replication     replica         192.168.10.0/24       md5

11. 创建归档路径
mkdir -p /usr/local/pgsql/archive
chown -R postgres:postgres /usr/local/pgsql/archive
chmod 700 /usr/local/pgsql/archive

12. 重启主节点
su postgres
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/logfile.log restart


--- 从节点
1. 解压软件包
tar -xf pg16-4_centos.tar -C /data

2. 创建pg用户
useradd postgres

3. 指定数据目录所有者和所属组
chown -R postgres:postgres /data/pgsql/data/

4. 创建软链接
ln -s /data/pgsql /usr/local/pgsql

5. 初始化从节点 --- 需提前移走pgsql目录下data的数据
mkdir -p /data/bak
mv /data/pgsql/data/* /data/bak/

6. 执行初始化 --- 初始化会复制主节点的配置文件，使用原来备份的postgresql.conf进行修改
/usr/local/pgsql/bin/pg_basebackup -h 192.168.10.126 -U replica -D /usr/local/pgsql/data -P -v --wal-method=stream
                                         主节点IP        复制用户

7. 创建从节点标识
touch /usr/local/pgsql/data/standby.signal

8. 创建归档路径
mkdir -p /usr/local/pgsql/archive
chown -R postgres:postgres /usr/local/pgsql/archive
chmod 700 /usr/local/pgsql/archive

9. 添加 postgresql.conf 相关配置，使用备份的配置
vim /data/bak/postgresql.conf
# 监听所有IP地址
listen_addresses = '*'
# 设置服务端口
port = 5432
# 设置最大连接数
max_connections = 300
# 允许从节点进行只读查询
hot_standby = on
# 设置从节点为只读模式
hot_standby_feedback = on
# 设置归档
restore_command = 'cp /usr/local/pgsql/archive/%f %p'
# 设置同步的主库信息
primary_conninfo = 'host=192.168.10.126 port=5432 user=replica password=5D0VieSajcmtBy'

10. 拷贝修改后的配置到相关位置
cp /data/bak/postgresql.conf  /data/pgsql/data/

11. 重新指定数据目录所有者和所属组
chown -R postgres:postgres /data/pgsql/data/

12. 启动pgsql从节点
su postgres
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/logfile.log start


主节点检查集群状态
/usr/local/pgsql/bin/psql -U postgres -d postgres
postgres=# SELECT pg_is_in_recovery();
postgres=# SELECT * FROM pg_stat_replication;

从节点检查复制状态
/usr/local/pgsql/bin/psql -U postgres -d postgres
postgres=# SELECT pg_is_in_recovery();
postgres=# SELECT * FROM pg_stat_wal_receiver;


主节点正常输出
postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

[当返回 f（false）：这表示当前的 PostgreSQL 实例不是处于恢复模式。对于一个集群而言，
这意味着它是一个正常的主节点（primary node），可以接受写操作，并且不会从其他节点接收WAL日志进行重放。]

postgres=# SELECT * FROM pg_stat_replication;
 pid  | usesysid | usename | application_name |  client_addr   | client_hostname | client_port |         backe
nd_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flu
sh_lag | replay_lag | sync_priority | sync_state |          reply_time           
------+----------+---------+------------------+----------------+-----------------+-------------+--------------
-----------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+----
-------+------------+---------------+------------+-------------------------------
 1686 |    16388 | replica | walreceiver      | 192.168.10.127 |                 |       56110 | 2025-02-11 22
:05:52.706362+08 |          749 | streaming | 0/7000148 | 0/7000148 | 0/7000148 | 0/7000148  |           |    
       |            |             1 | sync       | 2025-02-11 22:36:42.938936+08
 3305 |    16388 | replica | walreceiver      | 192.168.10.128 |                 |       45362 | 2025-02-11 22
:35:19.954433+08 |          749 | streaming | 0/7000148 | 0/7000148 | 0/7000148 | 0/7000148  |           |    
       |            |             1 | potential  | 2025-02-11 22:36:39.394938+08
(2 rows)


从节点正常输出
postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

[当返回 t（true）：这表示当前的 PostgreSQL 实例正处于恢复模式。
它通常是指一个只读的热备状态（hot standby），即从节点（standby node），从节点会不断地从主节点接收WAL日志并将其应用到自己的数据目录中。]

postgres=# SELECT * FROM pg_stat_wal_receiver;
 pid  |  status   | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |  
      latest_end_time        | slot_name |  sender_host   | sender_port |                                                                                                                
                                                        conninfo                                                                                                                         
                                                
------+-----------+-------------------+-------------------+-------------+-------------+--------------+-------------------------------+-------------------------------+----------------+--
-----------------------------+-----------+----------------+-------------+----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------
 2438 | streaming | 0/4000000         |                 1 | 0/7000148   | 0/7000148   |            1 | 2025-02-11 22:46:18.689259+08 | 2025-02-11 22:46:17.909612+08 | 0/7000148      | 2
025-02-11 22:24:18.394109+08 |           | 192.168.10.126 |        5432 | user=replica password=******** channel_binding=disable dbname=replication host=192.168.10.126 port=5432 fallbac
k_application_name=walreceiver sslmode=disable sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 targ
et_session_attrs=any load_balance_hosts=disable
(1 row)


测试数据同步情况，在主节点插入数据
-- 连接到主节点的数据库
/usr/local/pgsql/bin/psql -U postgres -d postgres

[也可创建一个新库测试
CREATE DATABASE testdb;
进入新库
/usr/local/pgsql/bin/psql -U postgres -d testdb]

-- 创建一个测试表
CREATE TABLE test_replication (
    id serial PRIMARY KEY,
    data text
);

-- 插入一些测试数据
INSERT INTO test_replication (data) VALUES ('Test Data 1'), ('Test Data 2');

-- 验证数据是否插入
SELECT * FROM test_replication;

检查从节点上的数据同步情况
-- 连接到从节点的数据库
/usr/local/pgsql/bin/psql -U postgres -d postgres

-- 验证从节点同步情况
SELECT * FROM test_replication;
 id |    data     
----+-------------
  1 | Test Data 1
  2 | Test Data 2
(2 rows)

-- 在大量数据插入的情况下 - 检查主从复制延迟情况(主节点上执行)
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS delay
FROM pg_stat_replication;

-- 检查主节点lsn值
postgres=# SELECT pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 32/A44DBBB8
(1 row)

-- 检查从节点lsn是否与主节点一致
postgres=# SELECT pg_last_wal_replay_lsn();
 pg_last_wal_replay_lsn
------------------------
 32/A44DBBB8
(1 row)

-- 查看当前pgsql已使用的连接数
SELECT count(*)
FROM pg_stat_activity;
